﻿/*
INSTALL TODOC

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.

NOTE: ON PROJECT DEPLOYMENT PROPERTIES ENSURE 
"DROP EXTENDED PROPERTIES NOT IN SOURCE" IS NOT CHECKED
*/

/*
-----------------------------------------------------------------------------
00 SCHEMA
-----------------------------------------------------------------------------
*/

-- CREATE TODOC SCHEMA
CREATE SCHEMA [todoc]
    AUTHORIZATION [dbo];
GO

/*
-----------------------------------------------------------------------------
00 USER AND ROLE
-----------------------------------------------------------------------------
*/

-- FOR ADVENTURE WORKS DEMO UNCOMMENT THIS SECTION
-- OR UNCOMMENT AND ADD YOUR SCHEMA NAMES IN HERE
-- IF YOU WANT TO USE A DEDICATED USER

/*
-- TODOC USER WITH NO LOGIN
CREATE USER [todocuser]
	WITHOUT LOGIN
	WITH DEFAULT_SCHEMA = todoc
GO

GRANT CONNECT TO [todocuser];
GO

CREATE ROLE [todocrole];
GO

EXEC sp_addrolemember 'todocrole', 'todocuser';
GO

GRANT CREATE PROCEDURE TO [todocrole];
GO

GRANT CREATE VIEW TO [todocrole];
GO

GRANT CREATE TABLE TO [todocrole];
GO

GRANT REFERENCES ON SCHEMA::dbo TO [todocrole];
GO

GRANT CONTROL ON SCHEMA::todoc TO [todocrole];
GO

GRANT CONTROL ON SCHEMA::ExtProps TO [todocrole];
GO

GRANT CONTROL ON SCHEMA::HumanResources TO [todocrole];
GO

GRANT CONTROL ON SCHEMA::Person TO [todocrole];
GO

GRANT CONTROL ON SCHEMA::Production TO [todocrole];
GO

GRANT CONTROL ON SCHEMA::Purchasing TO [todocrole];
GO

GRANT CONTROL ON SCHEMA::Sales TO [todocrole];
GO
*/

-- ACTIVE TABLES VIEW
CREATE VIEW [todoc].[svActiveTables]
AS
/*
LISTS ALL THE TABLES IN THE DATABASE THAT ARE ENABLED
FOR SOME FORM OF TODOC

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SELECT	SCHEMA_NAME(t.schema_id) AS TableSchema,
		t.[name] AS TableName,
		t.[schema_id],
		t.[object_id],
		MAX(CASE WHEN ep.[name] = 'Todoc: Enabled' THEN ep.value ELSE '' END) AS TodocEnabled,
		MAX(CASE WHEN ep.[name] = 'Todoc: Run' THEN ep.value ELSE '' END) AS TodocRun
FROM sys.extended_properties  ep
INNER JOIN sys.tables t
	ON ep.major_id = t.[object_id]
WHERE ep.class_desc = 'OBJECT_OR_COLUMN'
	AND ep.name IN('Todoc: Enabled', 'Todoc: Run')
GROUP BY	SCHEMA_NAME(t.schema_id),
			t.[name],
			t.[schema_id],
			t.[object_id];
GO

-- INDEX DETAILS VIEW
CREATE VIEW [todoc].[svIndexDetails]
/*
LISTS ALL THE DETAILS OF THE COLUMNS INDEX USAGE
FOR TODOC THIS IS PARTICIPATION IN THE PK OR
A NON-PK CLUSTERED INDEX

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
AS
SELECT	sic.[object_id],
		sic.[column_id],
		s.[name] AS TableSchema,
		st.[name] AS TableName,
		sc.[name] AS ColumnName, 
		si.[name] AS IDXName,
		si.[index_id] As IDXID,
		si.is_primary_key AS IDXPK,
		fg.[name] AS IDXFilegroup,
		sic.key_ordinal AS IDXColOrdinal,
		sic.is_descending_key AS IDXDescOrder,
		si.is_unique AS IDXUnique,
		ISNULL(ps.[name], '') AS IDXPartitionSchemeName,
		sic.partition_ordinal AS IDXPartitionOrdinal
FROM sys.columns sc
INNER JOIN sys.tables st
	ON sc.[object_id] = st.[object_id]
INNER JOIN sys.schemas s
	ON st.[schema_id] = s.[schema_id]
INNER JOIN sys.index_columns sic 
	ON sc.[object_id] = sic.[object_id]
	AND sc.column_id = sic.column_id
INNER JOIN sys.indexes si
	ON sic.[object_id] = si.[object_id] 
	AND sic.index_id = si.index_id
LEFT OUTER JOIN sys.filegroups fg
	ON si.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
	ON si.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions pf
	ON ps.function_id = ps.function_id
GROUP BY	sic.[object_id],
			sic.[column_id],
			s.[name],
			st.[name],
			sc.[name],
			si.[name],
			si.[index_id],
			si.is_primary_key,
			fg.[name],
			sic.key_ordinal,
			sic.is_descending_key,
			si.is_unique,
			ISNULL(ps.[name], ''),
			sic.partition_ordinal;
GO

-- NON-HEAP TABLES VIEW
CREATE VIEW [todoc].[svNonHeapTables]
AS
/*
LISTS ALL THE TABLES IN THE DATABASE THAT ARE NOT HEAPS
(AND ALSO NOT TODOC SHADOW/AUDIT TABLES)

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SELECT	ss.[name] AS TableSchema,
		st.[name] AS TableName,
		st.[schema_id],
		st.[object_id]
FROM sys.key_constraints kc
INNER JOIN sys.tables st
	ON kc.parent_object_id = st.[object_id]
INNER JOIN sys.schemas ss
	ON st.[schema_id] = ss.[schema_id]
WHERE kc.[type] = 'PK'
	AND ss.[name] NOT IN('dbo', 'todoc')
	AND st.[name] NOT LIKE '%' + 
		(SELECT CONVERT(nvarchar(128), ep.[value]) FROM sys.extended_properties ep
		WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
			AND ep.[name] = 'TODOC: Table name postfix');
GO

-- TABLE COLUMNS VIEW
CREATE VIEW [todoc].[svTableColumns]
AS
/*
LISTS ALL THE COLUMNS FOR TABLES IN THE DATABASE WITH ALL
THE DETAILS NEEDED FOR AUTOMATED OBJECT BUILDING

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SELECT	TableSchema,
		TableName,
		ColumnName,
		PKColumn, 
		PKName,
		PKClustered,
		PKFilegroup,
		PKColOrdinal,
		PKDescOrder,
		PKPartitionScheme,
		PKPartitionOrdinal,
		CLIDXName,
		CLIDXUnique,
		CLIDXFilegroup,
		CLIDXColOrdinal,
		CLIDXDescOrder,
		CLIDXPartitionScheme,
		CLIDXPartitionOrdinal,
		IdentityColumn,
		ColPosition,
		DataType,
		IsUserDataType,
		NumericPrecision,
		NumericScale,
		CharMaxLength,
		IsNullable,
		ISNULL(ColumnDefaultName, '') AS ColumnDefaultName,
		ColumnDefault,
		IsComputedCol,
		(CASE WHEN LOWER(DataType) IN(N'numeric',N'decimal') 
			THEN DataType + N'(' + CONVERT(NVARCHAR(10), NumericPrecision) + ',' + CONVERT(NVARCHAR(10), NumericScale) + ')' ELSE DataType END)
			+ (CASE WHEN CharMaxLength IS NOT NULL AND LOWER(DataType) NOT IN('hierarchyid') AND (CharMaxLength > 0 or (UPPER(DataType) IN(N'VARCHAR', N'NVARCHAR', N'VARBINARY') AND CharMaxLength = -1)) THEN '(' + CASE WHEN CONVERT(VARCHAR(10), CharMaxLength) = N'-1' THEN N'max' ELSE CONVERT(VARCHAR(10), CharMaxLength) END + N')' ELSE N'' END) + N' ' +
			+ (CASE WHEN IsNullable = 1 THEN
					CASE WHEN ColumnDefault IS NULL
						THEN N' = NULL'
						ELSE N''
					END
				ELSE
					CASE WHEN (ColumnDefault IS NULL AND IdentityColumn = 0) THEN N''
					ELSE
						CASE WHEN ISNULL(PKColumn, 0) = 1
							THEN N' = NULL'
							ELSE 
								CASE WHEN CHARINDEX(N'()', ColumnDefault) > 0 THEN N' = NULL'
								ELSE	' = ' + REPLACE((CASE 
													WHEN SUBSTRING(ColumnDefault, 1, 2) = N'(('
														THEN SUBSTRING(ColumnDefault, 3, LEN(ColumnDefault) - 4)
													WHEN SUBSTRING(ColumnDefault, 1, 1) = N'('
														THEN SUBSTRING(ColumnDefault, 2, LEN(ColumnDefault) - 2)
													ELSE REPLACE(REPLACE(ColumnDefault, N'(', N''), N')', N'')
												END), N'()', N'')
								END
							END
				END
		END) AS ParameterType,
		(CASE WHEN CHARINDEX(N'()', ColumnDefault) > 0
			THEN N'SET @' + ColumnName + ' = ' + SUBSTRING(ColumnDefault, 2, LEN(ColumnDefault) - 2) + ';'
			ELSE N''
		END) AS ParameterTypeSet,
		(
		(CASE WHEN DataType IN('numeric', 'decimal') THEN DataType + '(' + CONVERT(VARCHAR(10), NumericPrecision) + ',' + CONVERT(VARCHAR(10), NumericScale) + ')' ELSE DataType END) + 
		+ (CASE WHEN CharMaxLength IS NOT NULL AND LOWER(DataType) NOT IN('hierarchyid') AND (CharMaxLength > 0 or (UPPER(DataType) IN('VARCHAR', 'NVARCHAR', 'VARBINARY') AND CharMaxLength = -1)) THEN '(' + CASE WHEN CONVERT(VARCHAR(10), CharMaxLength) = '-1' THEN 'max' ELSE CONVERT(VARCHAR(10), CharMaxLength) END + ')' ELSE '' END) + ' ' +
		+ (CASE WHEN IdentityColumn = 1 THEN 'IDENTITY(1,1) ' ELSE '' END)
		+ (CASE WHEN IsNullable = 1 THEN 'NULL' ELSE 'NOT NULL' END)
		) AS TableColumnType,
		(
		(CASE WHEN DataType IN('numeric', 'decimal') THEN DataType + '(' + CONVERT(VARCHAR(10), NumericPrecision) + ',' + CONVERT(VARCHAR(10), NumericScale) + ')' ELSE DataType END) + 
		+ (CASE WHEN CharMaxLength IS NOT NULL AND LOWER(DataType) NOT IN('hierarchyid') AND (CharMaxLength > 0 or (UPPER(DataType) IN('VARCHAR', 'NVARCHAR', 'VARBINARY') AND CharMaxLength = -1)) THEN '(' + CASE WHEN CONVERT(VARCHAR(10), CharMaxLength) = '-1' THEN 'max' ELSE CONVERT(VARCHAR(10), CharMaxLength) END + ')' ELSE '' END) + ' ' +
		+ (CASE WHEN IsNullable = 1 THEN 'NULL' ELSE 'NOT NULL' END)
		) AS ShadowTableColumnType,
		tc.CheckConstraintName,
		tc.CheckConstraintDef
FROM
(
SELECT	s.[name] AS TableSchema,
		st.[name] AS TableName,
		sc.[name] AS ColumnName,
		ISNULL(pk.[IDXPK], 0) AS PKColumn, 
		ISNULL(pk.IDXName, '') AS PKName,
		(CASE WHEN pk.[IDXID] = 1 THEN 1 ELSE 0 END) AS PKClustered,
		ISNULL(pk.[IDXFilegroup], 'PRIMARY') AS PKFilegroup,
		ISNULL(pk.IDXColOrdinal, 0) AS PKColOrdinal,
		ISNULL(pk.IDXDescOrder, 0) AS PKDescOrder,
		(CASE 
			WHEN ISNULL(clidx.[IDXPartitionSchemeName], '') <> '' THEN '' 
			ELSE ISNULL(pk.[IDXPartitionSchemeName], '')
		END) AS PKPartitionScheme,
		(CASE 
			WHEN ISNULL(clidx.[IDXPartitionSchemeName], '') <> '' THEN 0 
			ELSE ISNULL(pk.[IDXPartitionOrdinal], 0)
		END) AS PKPartitionOrdinal,
		ISNULL(clidx.IDXName, '') AS CLIDXName,
		ISNULL(clidx.IDXUnique, 0) AS CLIDXUnique,
		ISNULL(clidx.[IDXFilegroup], 'PRIMARY') AS CLIDXFilegroup,
		ISNULL(clidx.IDXColOrdinal, 0) AS CLIDXColOrdinal,
		ISNULL(clidx.IDXDescOrder, 0) AS CLIDXDescOrder,
		ISNULL(clidx.[IDXPartitionSchemeName], '') AS CLIDXPartitionScheme,
		ISNULL(clidx.[IDXPartitionOrdinal], 0) AS CLIDXPartitionOrdinal,
		sc.is_identity AS IdentityColumn,
		sc.column_id AS ColPosition,
		(CASE 
			WHEN (sc.user_type_id <> sc.system_type_id) THEN
			-- put a schema on as a user defined type
			QUOTENAME(SCHEMA_NAME(sty.schema_id)) + '.' + QUOTENAME(sty.[name])
			ELSE sty.[name]
		END) AS DataType,
		(CASE WHEN sc.user_type_id <> sc.system_type_id THEN 1 ELSE 0 END) AS IsUserDataType,
		sc.[precision] AS NumericPrecision,
		sc.scale AS NumericScale,
		(CASE 
			WHEN sc.user_type_id <> sc.system_type_id THEN NULL 
			WHEN sty.[name] NOT IN('nvarchar', 'nchar', 'varchar', 'char', 'varbinary', 'binary') THEN NULL
			WHEN sty.[name] IN('nvarchar', 'nchar') AND sc.[max_length] <> - 1 THEN (sc.[max_length] / 2)
			ELSE sc.[max_length]
		END) AS CharMaxLength,
		sc.is_nullable AS IsNullable,
		sdc.[name] AS ColumnDefaultName,
		sdc.[definition] As ColumnDefault,
		sc.is_computed AS IsComputedCol,
		ISNULL(scc.[name], '') AS CheckConstraintName,
		ISNULL(scc.[definition], '') AS CheckConstraintDef
FROM sys.columns sc
INNER JOIN sys.tables st
	ON sc.[object_id] = st.[object_id]
INNER JOIN sys.schemas s
	ON st.[schema_id] = s.[schema_id]
INNER JOIN sys.types sty
	ON sc.user_type_id = sty.user_type_id
LEFT OUTER JOIN [todoc].[svIndexDetails] pk
	ON sc.[object_id] = pk.[object_id]
	AND sc.[column_id] = pk.[column_id]
	AND pk.IDXPK = 1
LEFT OUTER JOIN [todoc].[svIndexDetails] clidx
	ON sc.[object_id] = clidx.[object_id]
	AND sc.[column_id] = clidx.[column_id]
	AND clidx.IDXID = 1
	AND clidx.IDXPK = 0
LEFT OUTER JOIN sys.default_constraints sdc
	ON sc.default_object_id = sdc.[object_id]
LEFT OUTER JOIN sys.check_constraints scc
	ON sc.[object_id] = scc.parent_object_id
	AND sc.column_id = scc.parent_column_id
) tc;
GO

-- TABLE COLUMNS UDT
CREATE TYPE [todoc].[TableColumns] AS TABLE (
    [TableSchema]            SYSNAME         NOT NULL,
    [TableName]              SYSNAME         NOT NULL,
    [ColumnName]             SYSNAME         NOT NULL,
    [PKColumn]               BIT             NULL,
    [PKName]                 SYSNAME         NOT NULL,
    [PKClustered]            BIT             NULL,
    [PKFilegroup]            SYSNAME         NULL,
	[PKColOrdinal]           SMALLINT        NULL,
    [PKDescOrder]			 BIT             NULL,
    [PKPartitionScheme]      SYSNAME         NULL,
	[PKPartitionOrdinal]	 TINYINT		 NULL,
	[CLIDXName]              SYSNAME         NULL,
	[CLIDXUnique]            BIT			 NULL,
    [CLIDXFilegroup]         SYSNAME         NULL,
	[CLIDXColOrdinal]        SMALLINT        NULL,
    [CLIDXDescOrder]		 BIT             NULL,
    [CLIDXPartitionScheme]   SYSNAME         NULL,
	[CLIDXPartitionOrdinal]	 TINYINT		 NULL,
    [IdentityColumn]         BIT             NULL,
    [ColPosition]            INT             NULL,
    [DataType]               SYSNAME         NOT NULL,
    [IsUserDataType]         INT             NULL,
    [NumericPrecision]       TINYINT         NULL,
    [NumericScale]           INT             NULL,
    [CharMaxLength]          INT             NULL,
    [IsNullable]             BIT             NULL,
    [ColumnDefaultName]      SYSNAME         NULL,
    [ColumnDefault]          NVARCHAR (4000) NULL,
    [IsComputedCol]          BIT             NULL,
    [ParameterType]          NVARCHAR (200)  NULL,
    [ParameterTypeSet]       NVARCHAR (200)  NULL,
    [TableColumnType]        NVARCHAR (100)  NULL,
    [ShadowTableColumnType]  NVARCHAR (100)  NULL,
    [CheckConstraintName]    SYSNAME         NOT NULL,
    [CheckConstraintDef]     NVARCHAR (4000) NOT NULL);
GO

-- ADMINISTER OBJECTS
CREATE PROCEDURE [todoc].[sspAdministerObjects]
(
@TableSchema SYSNAME,
@TableName SYSNAME
)
--WITH EXECUTE AS 'todocuser'
AS
/*
GENERATES/REGENERATES ALL THE TODOC OBJECTS FOR A GIVEN TABLE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SET NOCOUNT ON;

DECLARE @HasView NVARCHAR(5) = 'False';
DECLARE @UsingShadowTable NVARCHAR(5) = 'False';
DECLARE @HasDeleteProc NVARCHAR(5) = 'False';
DECLARE @HasInsertProc NVARCHAR(5) = 'False';
DECLARE @HasUpdateProc NVARCHAR(5) = 'False';
DECLARE @HasSaveProc NVARCHAR(5) = 'False';
DECLARE @HasDeleteTrigger NVARCHAR(5) = 'False';
DECLARE @HasInsertTrigger NVARCHAR(5) = 'False';
DECLARE @HasUpdateTrigger NVARCHAR(5) = 'False';
DECLARE @UsingShadow BIT = 0;
DECLARE @ErrorSection NVARCHAR(20) = '';
DECLARE @TableColumns [todoc].[TableColumns];

BEGIN TRANSACTION;

BEGIN TRY

	INSERT INTO @TableColumns
	SELECT *
	FROM [todoc].[svTableColumns]
	WHERE TableSchema = @TableSchema
		AND TableName = @TableName;

	-- see what actions we need to take on this table
	SELECT	@HasView = MAX((CASE WHEN ep.[name] = 'Todoc: Has View' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@UsingShadowTable = MAX((CASE WHEN ep.[name] = 'Todoc: Has Shadow Table' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasDeleteProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Delete Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasInsertProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Insert Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasUpdateProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Update Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasSaveProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Save Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasDeleteTrigger = MAX((CASE WHEN ep.[name] = 'Todoc: Has Delete Trigger' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasInsertTrigger = MAX((CASE WHEN ep.[name] = 'Todoc: Has Insert Trigger' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasUpdateTrigger = MAX((CASE WHEN ep.[name] = 'Todoc: Has Update Trigger' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID(CONVERT(NVARCHAR(128), @TableSchema) + '.' + CONVERT(NVARCHAR(128), @TableName))
		AND ep.[name] LIKE 'Todoc: Has%';

	SET @ErrorSection = 'View';

	IF (@HasView = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropView]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		-- CREATE
		EXEC [todoc].[sspCreateView]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@TableColumnsCV = @TableColumns,
			@Debug = 0;

	END

	SET @ErrorSection = 'Shadow Table';

	IF (@UsingShadowTable = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropShadowTable]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		-- CREATE
		EXEC [todoc].[sspCreateShadowTable]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@TableColumnsCST = @TableColumns,
			@Debug = 0;

	END

	SET @ErrorSection = 'Delete SP';

	IF (@HasDeleteProc = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropDeleteSP]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
		
		IF (@UsingShadowTable = 'True' AND @HasDeleteTrigger = 'True')
		BEGIN
			-- has trigger so do not audit in the stored procedure
			SET @UsingShadow = 0;
		END
		ELSE
		BEGIN
			SET @UsingShadow = (CASE WHEN @UsingShadowTable = 'True' THEN 1 ELSE 0 END);
		END

		-- CREATE
		EXEC [todoc].[sspCreateDeleteSP]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCDSP = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;
	END

	SET @ErrorSection = 'Insert SP';

	IF (@HasInsertProc = 'True')
	BEGIN
		
		-- DROP
		EXEC [todoc].[sspDropInsertSP]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		IF (@UsingShadowTable = 'True' AND @HasInsertTrigger = 'True')
		BEGIN
			-- has trigger so do not audit in the stored procedure
			SET @UsingShadow = 0;
		END
		ELSE
		BEGIN
			SET @UsingShadow = (CASE WHEN @UsingShadowTable = 'True' THEN 1 ELSE 0 END);
		END
		
		-- CREATE
		EXEC [todoc].[sspCreateInsertSP]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCISP = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Update SP';

	IF (@HasUpdateProc = 'True')
	BEGIN
		
		-- DROP
		EXEC [todoc].[sspDropUpdateSP]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		IF (@UsingShadowTable = 'True' AND @HasUpdateTrigger = 'True')
		BEGIN
			-- has trigger so do not audit in the stored procedure
			SET @UsingShadow = 0;
		END
		ELSE
		BEGIN
			SET @UsingShadow = (CASE WHEN @UsingShadowTable = 'True' THEN 1 ELSE 0 END);
		END 
		
		-- CREATE
		EXEC [todoc].[sspCreateUpdateSP]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCUSP = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Save SP';

	IF (@HasSaveProc = 'True')
	BEGIN
		
		-- DROP
		EXEC [todoc].[sspDropSaveSP]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		IF (@UsingShadowTable = 'True' AND @HasInsertTrigger = 'True' AND @HasUpdateTrigger = 'True')
		BEGIN
			-- has triggers so do not audit in the stored procedure
			SET @UsingShadow = 0;
		END
		ELSE
		BEGIN
			SET @UsingShadow = (CASE WHEN @UsingShadowTable = 'True' THEN 1 ELSE 0 END);
		END
		
		-- CREATE
		EXEC [todoc].[sspCreateSaveSP]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCSSP = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Delete Trigger';

	IF (@HasDeleteTrigger = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropDeleteTrigger]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		-- CREATE
		EXEC [todoc].[sspCreateDeleteTrigger]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCDT = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Update Trigger';

	IF (@HasUpdateTrigger = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropUpdateTrigger]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
		
		-- CREATE
		EXEC [todoc].[sspCreateUpdateTrigger]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCUT = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Insert Trigger';

	IF (@HasInsertTrigger = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropInsertTrigger]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
		
		-- CREATE
		EXEC [todoc].[sspCreateInsertTrigger]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCIT = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	-- flag the table as having todo run via the extended property
	MERGE INTO ExtProps.Properties AS target
	USING
	(
	SELECT	@TableSchema,
			@TableName,
			NULL,
			N'Todoc: Run',
			'True'
	) AS source([object_schema], [object_name], [column_name], [name], [value])
    ON 
	(
	target.[object_schema] = source.[object_schema]
	AND target.[object_name] = source.[object_name]
	AND target.[name] = source.[name]
	)
    WHEN MATCHED THEN 
        UPDATE SET [value] = source.[value]
	WHEN NOT MATCHED THEN	
	    INSERT ([object_schema], [object_name], [column_name], [name], [value])
	    VALUES (source.[object_schema], source.[object_name], source.[column_name], source.[name], source.[value]);

	COMMIT TRANSACTION;

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = @ErrorSection + '_' + @TableSchema + '_' + @TableName + ': ' + ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	ROLLBACK TRANSACTION;

	RETURN -1;

END CATCH;
GO

-- CREATE DELETE
CREATE PROCEDURE [todoc].[sspCreateDeleteSP]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCDSP [todoc].[TableColumns] READONLY,
	@UsingShadow BIT,
	@Debug BIT = 0
)
AS
/*
GENERATES A DELETE STORED PROCEDURE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @PK_COLUMN_TYPE NVARCHAR(MAX) = '';
DECLARE @PK_COLUMN NVARCHAR(200) = '';
DECLARE @ALL_COLUMNS NVARCHAR(MAX) = '';
DECLARE @DeletedProcNamePrefix NVARCHAR(20) = '';
DECLARE @DeletedProcNamePostfix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = '';

BEGIN TRY 

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@DeletedProcNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@DeletedProcNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateDeleteSP')
		AND ep.[name] IN('Todoc: Procedure name prefix', 'Todoc: Procedure name postfix');

	IF (@UsingShadow = 1)
	BEGIN
		SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
				@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
		FROM sys.extended_properties ep
		WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
			AND ep.[name] IN('Todoc: Table name prefix', 'Todoc: Table name postfix');
	END

	-- string together the pk column(s) with no data type
	SELECT @PK_COLUMN += QUOTENAME(ColumnName) + ' = @' + ColumnName  + ' AND '
	FROM @TableColumnsCDSP
	WHERE PKColumn = 1
	ORDER BY ColPosition;
	SET @PK_COLUMN = SUBSTRING(@PK_COLUMN, 1, LEN(@PK_COLUMN) - 4);
	
	-- string together the pk column(s) details
	SELECT @PK_COLUMN_TYPE += '@' + ColumnName 
			+ ' as ' 
			+ (CASE DataType WHEN 'numeric' THEN DataType + '(' + CONVERT(VARCHAR(10), NumericPrecision) + ',' + CONVERT(VARCHAR(10), NumericScale) + ')' ELSE DataType END)
			+ (CASE WHEN CharMaxLength IS NOT NULL AND DataType NOT IN('hierarchyid') THEN '(' + CASE WHEN CONVERT(VARCHAR(10), CharMaxLength) = '-1' THEN 'MAX' ELSE CONVERT(VARCHAR(10), CharMaxLength) END + ')' ELSE '' END)
			+ ',' + CHAR(13) 
	FROM @TableColumnsCDSP
	WHERE PKColumn = 1
	ORDER BY ColPosition;
	SET @PK_COLUMN_TYPE = SUBSTRING(@PK_COLUMN_TYPE, 1, LEN(@PK_COLUMN_TYPE) - 2);

	-- string together all the columns
	SELECT @ALL_COLUMNS += 'DELETED.' + QUOTENAME(ColumnName) + ','
	FROM @TableColumnsCDSP
	ORDER BY ColPosition;
	SET @ALL_COLUMNS = SUBSTRING(@ALL_COLUMNS, 1, LEN(@ALL_COLUMNS) - 1);
	
	-- create and parameters (pks)
	SET @SQL += 'CREATE PROC [' + @TableSchema + '].[' + @DeletedProcNamePrefix + @TableName + @DeletedProcNamePostfix + ']' + CHAR(13);
	SET @SQL += '(' + CHAR(13);
	SET @SQL += @PK_COLUMN_TYPE + CHAR(13);
	SET @SQL += ')' + CHAR(13);
	SET @SQL += 'AS' + CHAR(13);
	SET @SQL += '' + CHAR(13);
	
	-- YAML metadata here
	SET @SQL += '/**' + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'summary:	>' + CHAR(13);
	SET @SQL += '				Deletes an ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table record' + CHAR(13);
	SET @SQL += ' - code:	EXEC [' + @TableSchema + '].[' + @DeletedProcNamePrefix + @TableName + @DeletedProcNamePostfix + ']' + CHAR(13);
	SET @SQL += '	parameters: ' + REPLACE(REPLACE(@PK_COLUMN_TYPE, CHAR(13), ''), ' as ', ' : ') + CHAR(13);
	SET @SQL += 'returns: 0 if success, otherwise -1' + CHAR(13);
	SET @SQL += '**/' + CHAR(13);
	
	-- body
	SET @SQL += 'BEGIN TRANSACTION' + CHAR(13) + CHAR(13); 
	SET @SQL += 'BEGIN TRY' + CHAR(13) + CHAR(13);
	SET @SQL += '	-- DELETE' + CHAR(13);
	SET @SQL += '	DELETE FROM [' + @TableSchema + '].[' + @TableName + ']' + CHAR(13);
	IF (@UsingShadow = 1)
	BEGIN
		SET @SQL += '	OUTPUT ''D'',' + REPLACE(@ALL_COLUMNS, 'DELETED.[ModifiedDate]', 'GETDATE()') + CHAR(13);
		SET @SQL += '	INTO [' + @TableSchema + '].[' + @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix + ']';
		SET @SQL += '([' + REPLACE(@ShadowTableNamePostfix, '_', '') + 'Type], ' + REPLACE(@ALL_COLUMNS, 'Deleted.', '') + ')' + CHAR(13);
	END
	SET @SQL += '	WHERE ' + @PK_COLUMN + ';' + CHAR(13) + CHAR(13);
	SET @SQL += '	COMMIT TRANSACTION' + CHAR(13) + CHAR(13);
	SET @SQL += '	RETURN 0;' + CHAR(13) + CHAR(13);
	SET @SQL += 'END TRY' + CHAR(13) + CHAR(13);

	-- catch block
	SET @SQL += 'BEGIN CATCH' + CHAR(13) + CHAR(13);
	SET @SQL += '	DECLARE @ErrorMessage NVARCHAR(4000);' + CHAR(13);
	SET @SQL += '	DECLARE @ErrorSeverity INT;' + CHAR(13);
	SET @SQL += '	DECLARE @ErrorState INT;' + CHAR(13) + CHAR(13);
	SET @SQL += '	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + CHAR(13) + CHAR(13);
	SET @SQL += '	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);' + CHAR(13) + CHAR(13);
	SET @SQL += '	ROLLBACK TRANSACTION' + CHAR(13) + CHAR(13);
	SET @SQL += '	RETURN -1;' + CHAR(13) + CHAR(13);
	SET @SQL += 'END CATCH;' + CHAR(13) + CHAR(13);
  
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO
EXECUTE sp_addextendedproperty @name = N'Todoc: Procedure name prefix', @value = N'asp', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateDeleteSP';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: Procedure name postfix', @value = N'_Delete', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateDeleteSP';
GO

-- CREATE DELETE TRIGGER
CREATE PROCEDURE [todoc].[sspCreateDeleteTrigger]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCDT [todoc].[TableColumns] READONLY,
	@UsingShadow BIT,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE CREATION OF AN UPDATE TRIGGER

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @COLUMNS_NO_DATA_TYPE_QUOTED NVARCHAR(MAX) = '';
DECLARE @TriggerNamePrefix NVARCHAR(20) = '';
DECLARE @TriggerNamePostfix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = ''; 
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = ''; 

BEGIN TRY 

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@TriggerNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@TriggerNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateDeleteTrigger')
		AND ep.[name] IN('Todoc: Trigger name prefix', 'Todoc: Trigger name postfix');

	SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
		AND ep.[name] IN('Todoc: Table name prefix', 'Todoc: Table name postfix');

	-- get a list of all columns with no datatype (quoted)
	SELECT @COLUMNS_NO_DATA_TYPE_QUOTED += QUOTENAME(ColumnName) + ','
	FROM @TableColumnsCDT
	SET @COLUMNS_NO_DATA_TYPE_QUOTED = SUBSTRING(@COLUMNS_NO_DATA_TYPE_QUOTED, 1, LEN(@COLUMNS_NO_DATA_TYPE_QUOTED) - 1);

	-- create and parameters
	SET @SQL += 'CREATE TRIGGER [' + @TableSchema + '].[' + @TriggerNamePrefix + @TableName + @TriggerNamePostfix + ']' + CHAR(13)
	SET @SQL += 'ON [' + @TableSchema + '].[' + @TableName + ']' + CHAR(13)
	SET @SQL += 'FOR DELETE' + CHAR(13)
	SET @SQL += 'AS' + CHAR(13)
	SET @SQL += '' + CHAR(13)
	
	-- YAML metadata here
	SET @SQL += '/**' + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'summary:	>' + CHAR(13);
	SET @SQL += '				Records the delete of an ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table record' + CHAR(13);
	SET @SQL += ' - code:	Cannot be called from client code' + CHAR(13);
	SET @SQL += '	parameters: n/a' + CHAR(13);
	SET @SQL += 'returns: on success nothing, otherwise throws an error' + CHAR(13);
	SET @SQL += '**/' + CHAR(13);
	
	-- body
	SET @SQL += 'BEGIN TRY' +  + CHAR(13) + CHAR(13) 
	SET @SQL += '	INSERT INTO [' + @TableSchema + '].[' + @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix + ']' + CHAR(13);
	SET @SQL += '	([' + RIGHT(@ShadowTableNamePostfix, LEN(@ShadowTableNamePostfix) - 1) + 'Type],' + @COLUMNS_NO_DATA_TYPE_QUOTED + ')' + CHAR(13);
	SET @SQL += '	SELECT ''D'',' + REPLACE(REPLACE(@COLUMNS_NO_DATA_TYPE_QUOTED, '[', '[DELETED].['), 'DELETED.[ModifiedDate]', 'GETDATE()') + CHAR(13);
	SET @SQL += '	FROM DELETED;' + CHAR(13) + CHAR(13);
	SET @SQL += 'END TRY' + CHAR(13) + CHAR(13)
	
	-- catch block
	SET @SQL += 'BEGIN CATCH' + CHAR(13) + CHAR(13)  
	SET @SQL += '	DECLARE @ErrorMessage NVARCHAR(4000);' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorSeverity INT;' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorState INT;' + CHAR(13) + CHAR(13)
	SET @SQL += '	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + CHAR(13) + CHAR(13)
	SET @SQL += '	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);' + CHAR(13) + CHAR(13) 
	SET @SQL += 'END CATCH;' + CHAR(13) + CHAR(13)
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END
	
	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO
EXECUTE sp_addextendedproperty @name = N'Todoc: Trigger name prefix', @value = N'atr', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateDeleteTrigger';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: Trigger name postfix', @value = N'_Delete', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateDeleteTrigger';
GO

-- CREATE INSERT
CREATE PROCEDURE [todoc].[sspCreateInsertSP]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCISP [todoc].[TableColumns] READONLY,
	@UsingShadow BIT,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE CREATION OF AN INSERT STORED PROCEDURE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @PARAMETER_COLUMNS NVARCHAR(MAX) = '';
DECLARE @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED NVARCHAR(MAX) = '';
DECLARE @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED_PARAMS NVARCHAR(MAX) = '';
DECLARE @COLUMNS_NO_DATA_TYPE_QUOTED NVARCHAR(MAX) = '';
DECLARE @PARAMETER_SETS NVARCHAR(MAX) = '';
DECLARE @PK_COLUMN NVARCHAR(2000) = '';
DECLARE @IDENTITY_COL NVARCHAR(200);
DECLARE @InsertProcNamePrefix NVARCHAR(20) = '';
DECLARE @InsertProcNamePostfix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = '';

BEGIN TRY 

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@InsertProcNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@InsertProcNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateInsertSP')
		AND ep.[name] IN('Todoc: Procedure name prefix', 'Todoc: Procedure name postfix');

	IF (@UsingShadow = 1)
	BEGIN
		SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
				@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
		FROM sys.extended_properties ep
		WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
			AND ep.[name] IN('Todoc: Table name prefix', 'Todoc: Table name postfix');
	END

	-- string together the pk column(s) with no data type
	SELECT @PK_COLUMN += QUOTENAME(ColumnName) + ' = @' + ColumnName  + ' AND '
	FROM @TableColumnsCISP
	WHERE PKColumn = 1
	ORDER BY ColPosition;
	SET @PK_COLUMN = SUBSTRING(@PK_COLUMN, 1, LEN(@PK_COLUMN) - 4);

	-- get the identity column if there is one
	SELECT @IDENTITY_COL = '@' + ColumnName
	FROM @TableColumnsCISP
	WHERE IdentityColumn = 1
	
	-- build the parameter list (all columns and datatypes with defaults)
	SELECT @PARAMETER_COLUMNS += '@' + ColumnName + ' as ' + ParameterType + ',' + CHAR(13)
	FROM @TableColumnsCISP
	WHERE IsComputedCol = 0
	ORDER BY ColPosition;
	SET @PARAMETER_COLUMNS = SUBSTRING(@PARAMETER_COLUMNS, 1, LEN(@PARAMETER_COLUMNS) - 2);

	-- get a list of all columns with no datatype (quoted)
	SELECT @COLUMNS_NO_DATA_TYPE_QUOTED += QUOTENAME(ColumnName) + ','
	FROM @TableColumnsCISP
	ORDER BY ColPosition;
	SET @COLUMNS_NO_DATA_TYPE_QUOTED = SUBSTRING(@COLUMNS_NO_DATA_TYPE_QUOTED, 1, LEN(@COLUMNS_NO_DATA_TYPE_QUOTED) - 1);

	-- get a list of all columns with no datatype minus the pk column(s) (quoted)
	SELECT @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED += QUOTENAME(ColumnName) + ','
	FROM @TableColumnsCISP
	WHERE IsComputedCol = 0
		AND (PKColumn = 0 OR (PKColumn = 1 AND IdentityColumn = 0))
	ORDER BY ColPosition;
	SET @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED = SUBSTRING(@COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED, 1, LEN(@COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED) - 1);

	-- get a list of all columns with no datatype minus the pk column(s) (parameters)
	SELECT @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED_PARAMS += '@' + ColumnName + ','
	FROM @TableColumnsCISP
	WHERE IsComputedCol = 0
		AND (PKColumn = 0 OR (PKColumn = 1 AND IdentityColumn = 0))
	ORDER BY ColPosition;
	SET @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED_PARAMS = SUBSTRING(@COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED_PARAMS, 1, LEN(@COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED_PARAMS) - 1);

	SELECT @PARAMETER_SETS += ParameterTypeSet + CHAR(13)
	FROM @TableColumnsCISP
	WHERE ParameterTypeSet <> ''
	ORDER BY ColPosition;

	-- create and parameters
	SET @SQL += 'CREATE PROC [' + @TableSchema + '].[' + @InsertProcNamePrefix + @TableName + @InsertProcNamePostfix + ']' + CHAR(13);
	SET @SQL += '(' + CHAR(13);
	SET @SQL += @PARAMETER_COLUMNS + CHAR(13);
	SET @SQL += ')' + CHAR(13);
	SET @SQL += 'AS' + CHAR(13);
	SET @SQL += '' + CHAR(13);
	
	-- YAML metadata here
	SET @SQL += '/**' + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'summary:	>' + CHAR(13);
	SET @SQL += '				Creates an ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table record' + CHAR(13);
	SET @SQL += ' - code:	EXEC [' + @TableSchema + '].[' + @InsertProcNamePrefix + @TableName + @InsertProcNamePostfix + ']' + CHAR(13);
	SET @SQL += '	parameters: ' + REPLACE(REPLACE(@PARAMETER_COLUMNS, CHAR(13), ''), ' as ', ' : ') + CHAR(13);
	IF ISNULL(@IDENTITY_COL, '') = ''
	BEGIN
		SET @SQL += 'returns: 0 if success, otherwise -1' + CHAR(13);
	END
	ELSE
	BEGIN
		SET @SQL += 'returns: new ' + REPLACE(CONVERT(SYSNAME, @IDENTITY_COL), '@', '') + ' value if success, otherwise -1' + CHAR(13);
	END
	SET @SQL += '**/' + CHAR(13);
	
	-- body
	SET @SQL += 'BEGIN TRANSACTION;' + CHAR(13) + CHAR(13)
	SET @SQL += 'BEGIN TRY' + CHAR(13) + CHAR(13)
	IF (@PARAMETER_SETS <> '')
		SET @SQL += @PARAMETER_SETS + CHAR(13)
	SET @SQL += '	INSERT INTO  [' + @TableSchema + '].[' + @TableName + '](' + @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED + ')' + CHAR(13)
	IF (@UsingShadow = 1)
	BEGIN
		SET @SQL += 'OUTPUT ''I'', ' + REPLACE(@COLUMNS_NO_DATA_TYPE_QUOTED, '[', '[INSERTED].[')
		SET @SQL += '	INTO [' + @TableSchema + '].[' + @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix + ']([' + Replace(@ShadowTableNamePostfix, '_', '') + 'Type],' + @COLUMNS_NO_DATA_TYPE_QUOTED + ')' + CHAR(13)
	END
	SET @SQL += '	VALUES (' + @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED_PARAMS + ');' + CHAR(13) + CHAR(13)
	IF (ISNULL(@IDENTITY_COL, '') <> '')
		SET @SQL += '	SET ' + @IDENTITY_COL + ' = SCOPE_IDENTITY();' + CHAR(13) + CHAR(13)
	SET @SQL += '	COMMIT TRANSACTION;' + CHAR(13) + CHAR(13)
	SET @SQL += '	RETURN ' + ISNULL(@IDENTITY_COL, 0) + ';' + CHAR(13) + CHAR(13)
	SET @SQL += 'END TRY' + CHAR(13) + CHAR(13)
	
	-- catch block
	SET @SQL += 'BEGIN CATCH' + CHAR(13) + CHAR(13)  
	SET @SQL += '	DECLARE @ErrorMessage NVARCHAR(4000);' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorSeverity INT;' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorState INT;' + CHAR(13) + CHAR(13)
	SET @SQL += '	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + CHAR(13) + CHAR(13)
	SET @SQL += '	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);' + CHAR(13) + CHAR(13)
	SET @SQL += '	ROLLBACK TRANSACTION;' + CHAR(13) + CHAR(13) 
	SET @SQL += '	RETURN -1;' + CHAR(13) + CHAR(13) 
	SET @SQL += 'END CATCH;' + CHAR(13) + CHAR(13)
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END
	
	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO
EXECUTE sp_addextendedproperty @name = N'Todoc: Procedure name prefix', @value = N'asp', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateInsertSP';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: Procedure name postfix', @value = N'_Insert', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateInsertSP';
GO

-- CREATE INSERT TRIGGER
CREATE PROCEDURE [todoc].[sspCreateInsertTrigger]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCIT [todoc].[TableColumns] READONLY,
	@UsingShadow BIT,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE CREATION OF AN INSERT TRIGGER

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @COLUMNS_NO_DATA_TYPE_QUOTED NVARCHAR(MAX) = '';
DECLARE @TriggerNamePrefix NVARCHAR(20) = '';
DECLARE @TriggerNamePostfix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = ''; 

BEGIN TRY 

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@TriggerNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@TriggerNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateInsertTrigger')
		AND ep.[name] IN('Todoc: Trigger name prefix', 'Todoc: Trigger name postfix');

	SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
		AND ep.[name] IN('Todoc: Table name prefix', 'Todoc: Table name postfix');

	-- get a list of all columns with no datatype (quoted)
	SELECT @COLUMNS_NO_DATA_TYPE_QUOTED += QUOTENAME(ColumnName) + ','
	FROM @TableColumnsCIT
	ORDER BY ColPosition;
	SET @COLUMNS_NO_DATA_TYPE_QUOTED = SUBSTRING(@COLUMNS_NO_DATA_TYPE_QUOTED, 1, LEN(@COLUMNS_NO_DATA_TYPE_QUOTED) - 1);

	-- create and parameters
	SET @SQL += 'CREATE TRIGGER [' + @TableSchema + '].[' + @TriggerNamePrefix + @TableName + @TriggerNamePostfix + ']' + CHAR(13)
	SET @SQL += 'ON [' + @TableSchema + '].[' + @TableName + ']' + CHAR(13)
	SET @SQL += 'FOR INSERT' + CHAR(13)
	SET @SQL += 'AS' + CHAR(13)
	SET @SQL += '' + CHAR(13)
	
	-- YAML metadata here
	SET @SQL += '/**' + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'summary:	>' + CHAR(13);
	SET @SQL += '				Records the insert of an ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table record' + CHAR(13);
	SET @SQL += ' - code:	Cannot be called from client code' + CHAR(13);
	SET @SQL += '	parameters: n/a' + CHAR(13);
	SET @SQL += 'returns: on success nothing, otherwise throws an error' + CHAR(13);
	SET @SQL += '**/' + CHAR(13);
	
	-- body
	SET @SQL += 'BEGIN TRY' +  + CHAR(13) +  + CHAR(13) 
	SET @SQL += '	INSERT INTO [' + @TableSchema + '].[' + @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix + ']' + CHAR(13);
	SET @SQL += '	([' + RIGHT(@ShadowTableNamePostfix, LEN(@ShadowTableNamePostfix) - 1) + 'Type],' + @COLUMNS_NO_DATA_TYPE_QUOTED + ')' + CHAR(13);
	SET @SQL += '	SELECT ''I'',' + REPLACE(@COLUMNS_NO_DATA_TYPE_QUOTED, '[', '[INSERTED].[') + CHAR(13);
	SET @SQL += '	FROM INSERTED;' + CHAR(13) + CHAR(13);
	SET @SQL += 'END TRY' + CHAR(13) + CHAR(13)
	
	-- catch block
	SET @SQL += 'BEGIN CATCH' + CHAR(13) + CHAR(13)  
	SET @SQL += '	DECLARE @ErrorMessage NVARCHAR(4000);' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorSeverity INT;' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorState INT;' + CHAR(13) + CHAR(13)
	SET @SQL += '	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + CHAR(13) + CHAR(13)
	SET @SQL += '	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);' + CHAR(13) + CHAR(13) 
	SET @SQL += 'END CATCH;' + CHAR(13) + CHAR(13)
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO
EXECUTE sp_addextendedproperty @name = N'Todoc: Trigger name prefix', @value = N'atr', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateInsertTrigger';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: Trigger name postfix', @value = N'_Insert', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateInsertTrigger';
GO

-- CREATE SAVE
CREATE PROCEDURE [todoc].[sspCreateSaveSP]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCSSP [todoc].[TableColumns] READONLY,
	@UsingShadow BIT,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE CREATION OF A SAVE (COMBINED INSERT/UPDATE) 
STORED PROCEDURE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @PARAMETER_COLUMNS NVARCHAR(MAX) = '';
DECLARE @COLUMN_LIST_NO_DATA_TYPE NVARCHAR(MAX) = '';
DECLARE @COLUMN_LIST_NO_DATA_TYPE_UPDATE NVARCHAR(MAX) = '';
DECLARE @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK NVARCHAR(MAX) = '';
DECLARE @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED NVARCHAR(MAX) = '';
DECLARE @COLUMNS_NO_DATA_TYPE_QUOTED NVARCHAR(MAX) = '';
DECLARE @PARAMETER_SETS NVARCHAR(MAX) = '';
DECLARE @PK_COLUMN NVARCHAR(2000) = '';
DECLARE @IDENTITY_COL NVARCHAR(200);
DECLARE @SaveProcNamePrefix NVARCHAR(20) = '';
DECLARE @SaveProcNamePostfix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = '';

BEGIN TRY 

	-- get the postfixs we're using
	SELECT	@SaveProcNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@SaveProcNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateSaveSP')
		AND ep.[name] IN('Todoc: Procedure name prefix', 'Todoc: Procedure name postfix');

	IF (@UsingShadow = 1)
	BEGIN
		SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
				@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
		FROM sys.extended_properties ep
		WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
			AND ep.[name] IN('Todoc: Table name prefix', 'Todoc: Table name postfix');
	END

	-- string together the pk column(s) with no data type
	SELECT @PK_COLUMN += QUOTENAME(ColumnName) + ' = @' + ColumnName  + ' AND '
	FROM @TableColumnsCSSP
	WHERE PKColumn = 1
	ORDER BY ColPosition;
	SET @PK_COLUMN = SUBSTRING(@PK_COLUMN, 1, LEN(@PK_COLUMN) - 4);

	-- get the identity column if there is one
	SELECT @IDENTITY_COL = '@' + ColumnName
	FROM @TableColumnsCSSP
	WHERE IdentityColumn = 1
	
	-- build the parameter list (all columns and datatypes with defaults)
	SELECT @PARAMETER_COLUMNS += '@' + ColumnName + ' as ' + ParameterType + ',' + CHAR(13) 
	FROM @TableColumnsCSSP
	WHERE IsComputedCol = 0
	ORDER BY ColPosition;
	SET @PARAMETER_COLUMNS = SUBSTRING(@PARAMETER_COLUMNS, 1, LEN(@PARAMETER_COLUMNS) - 2);

	-- get a list of all columns with no datatype
	SELECT @COLUMN_LIST_NO_DATA_TYPE += '@' + ColumnName + ','
	FROM @TableColumnsCSSP
	WHERE IsComputedCol = 0
	ORDER BY ColPosition;
	SET @COLUMN_LIST_NO_DATA_TYPE = SUBSTRING(@COLUMN_LIST_NO_DATA_TYPE, 1, LEN(@COLUMN_LIST_NO_DATA_TYPE) - 1);

	-- get a list of all columns with no datatype (quoted)
	SELECT @COLUMNS_NO_DATA_TYPE_QUOTED += QUOTENAME(ColumnName) + ','
	FROM @TableColumnsCSSP
	WHERE IsComputedCol = 0
	ORDER BY ColPosition;
	SET @COLUMNS_NO_DATA_TYPE_QUOTED = SUBSTRING(@COLUMNS_NO_DATA_TYPE_QUOTED, 1, LEN(@COLUMNS_NO_DATA_TYPE_QUOTED) - 1);

	-- get a list of all columns with no datatype minus the pk identity column(s) for update statement
	SELECT @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK += '@' + ColumnName + ','
	FROM @TableColumnsCSSP
	WHERE IsComputedCol = 0
		AND (PKColumn = 0 OR (PKColumn = 1 AND IdentityColumn = 0))
	ORDER BY ColPosition;
	SET @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK = SUBSTRING(@COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK, 1, LEN(@COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK) - 1);

	-- get a list of all columns with no datatype minus the pk column(s) (quoted)
	SELECT @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED += '[' + ColumnName + '],'
	FROM @TableColumnsCSSP
	WHERE IsComputedCol = 0
		AND (PKColumn = 0 OR (PKColumn = 1 AND IdentityColumn = 0))
	ORDER BY ColPosition;
	SET @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED = SUBSTRING(@COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED, 1, LEN(@COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED) - 1);

	-- get a list of all columns with no datatype minus the pk column(s) for update statement
	SELECT @COLUMN_LIST_NO_DATA_TYPE_UPDATE += QUOTENAME(ColumnName) + ' = @' + ColumnName + ','
	FROM @TableColumnsCSSP
	WHERE IsComputedCol = 0
		AND PKColumn = 0
	ORDER BY ColPosition;
	SET @COLUMN_LIST_NO_DATA_TYPE_UPDATE = SUBSTRING(@COLUMN_LIST_NO_DATA_TYPE_UPDATE, 1, LEN(@COLUMN_LIST_NO_DATA_TYPE_UPDATE) - 1);

	SELECT @PARAMETER_SETS += ParameterTypeSet + CHAR(13)
	FROM @TableColumnsCSSP
	WHERE ParameterTypeSet <> ''
	ORDER BY ColPosition;

	-- create and parameters
	SET @SQL += 'CREATE PROC [' + @TableSchema + '].[' + @SaveProcNamePrefix + @TableName + @SaveProcNamePostfix + ']' + CHAR(13)
	SET @SQL += '(' + CHAR(13)
	SET @SQL += @PARAMETER_COLUMNS + CHAR(13)
	SET @SQL += ')' + CHAR(13)
	SET @SQL += 'AS' + CHAR(13)
	SET @SQL += '' + CHAR(13)
	
	-- YAML metadata here
	SET @SQL += '/**' + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'summary:	>' + CHAR(13);
	SET @SQL += '				Creates or updates an ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table record' + CHAR(13);
	SET @SQL += ' - code:	EXEC [' + @TableSchema + '].[' + @SaveProcNamePrefix + @TableName + @SaveProcNamePostfix + ']';
	SET @SQL += '	parameters: ' + REPLACE(REPLACE(@PARAMETER_COLUMNS, CHAR(13), ''), ' as ', ' : ') + CHAR(13);
	IF ISNULL(@IDENTITY_COL, '') = ''
	BEGIN
		SET @SQL += 'returns: 0 if success, otherwise -1' + CHAR(13);
	END
	ELSE
	BEGIN
		SET @SQL += 'returns: new (on insert) or passed in (on update) ' + REPLACE(CONVERT(SYSNAME, @IDENTITY_COL), '@', '') + ' value if success, otherwise -1' + CHAR(13);
	END
	SET @SQL += '**/' + CHAR(13);
	
	-- body
	SET @SQL += 'BEGIN TRANSACTION;' + char(13) + char(13)
	SET @SQL += 'BEGIN TRY' + char(13) + char(13) 
	SET @SQL += '-- UPDATE' + char(13)
	SET @SQL += 'UPDATE [' + @TableSchema + '].[' + @TableName + '] SET ' + char(13)
	SET @SQL += '	' + @COLUMN_LIST_NO_DATA_TYPE_UPDATE + char(13)
	IF (@UsingShadow = 1)
	BEGIN
		SET @SQL += '	OUTPUT ''U'',' + REPLACE(REPLACE(@COLUMNS_NO_DATA_TYPE_QUOTED, '[', '[DELETED].['), 'DELETED.[ModifiedDate]', 'GETDATE()') + CHAR(13);
		SET @SQL += '	INTO [' + @TableSchema + '].[' + @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix + ']';
		SET @SQL += '([' + REPLACE(@ShadowTableNamePostfix, '_', '') + 'Type], ' + @COLUMNS_NO_DATA_TYPE_QUOTED + ')' + CHAR(13);
	END
	SET @SQL += '	WHERE ' + @PK_COLUMN + ';' + CHAR(13) + CHAR(13);
	SET @SQL += 'IF (@@ROWCOUNT = 0)' + CHAR(13)
	SET @SQL += 'BEGIN' + CHAR(13)
	IF (@PARAMETER_SETS <> '')
		SET @SQL += @PARAMETER_SETS + CHAR(13)
	SET @SQL += '	INSERT INTO  [' + @TableSchema + '].[' + @TableName + '](' + @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK_QUOTED + ')' + CHAR(13)
	IF (@UsingShadow = 1)
	BEGIN
		SET @SQL += 'OUTPUT ''I'', ' + REPLACE(@COLUMNS_NO_DATA_TYPE_QUOTED, '[', '[INSERTED].[')
		SET @SQL += '	INTO [' + @TableSchema + '].[' + @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix + ']([' + Replace(@ShadowTableNamePostfix, '_', '') + 'Type],' + @COLUMNS_NO_DATA_TYPE_QUOTED + ')' + CHAR(13)
	END
	SET @SQL += '	VALUES (' + @COLUMN_LIST_NO_DATA_TYPE_MINUS_IDENTITY_PK + ');' + CHAR(13) + CHAR(13)
	IF (ISNULL(@IDENTITY_COL, '') <> '')
		SET @SQL += '	SET ' + @IDENTITY_COL + ' = SCOPE_IDENTITY();' + CHAR(13) + CHAR(13)
	SET @SQL += 'END' + CHAR(13) + CHAR(13)
	SET @SQL += '	COMMIT TRANSACTION;' + CHAR(13) + CHAR(13)
	SET @SQL += '	RETURN ' + ISNULL(@IDENTITY_COL, 0) + ';' + CHAR(13) + CHAR(13)
	SET @SQL += 'END TRY' + CHAR(13) + CHAR(13)
	
	-- catch block
	SET @SQL += 'BEGIN CATCH' + CHAR(13) + CHAR(13)  
	SET @SQL += '	DECLARE @ErrorMessage NVARCHAR(4000);' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorSeverity INT;' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorState INT;' + CHAR(13) + CHAR(13)
	SET @SQL += '	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + CHAR(13) + CHAR(13)
	SET @SQL += '	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);' + CHAR(13) + CHAR(13)
	SET @SQL += '	ROLLBACK TRANSACTION;' + CHAR(13) + CHAR(13) 
	SET @SQL += '	RETURN -1;' + CHAR(13) + CHAR(13) 
	SET @SQL += 'END CATCH;' + CHAR(13) + CHAR(13)
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO
EXECUTE sp_addextendedproperty @name = N'Todoc: Procedure name prefix', @value = N'aspSave', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateSaveSP';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: Procedure name postfix', @value = N'_Save', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateSaveSP';
GO

-- CREATE SHADOW/AUDIT TABLE
CREATE PROCEDURE [todoc].[sspCreateShadowTable]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCST [todoc].[TableColumns] READONLY,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE CREATION OR ALTERATION OF A SHADOW/AUDIT TABLE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @PK_SQL NVARCHAR(MAX) = '';
DECLARE @CLIDX_SQL NVARCHAR(MAX) = '';
DECLARE @TYPE_DEFAULT_SQL NVARCHAR(MAX) = '';
DECLARE @PK_NAME SYSNAME = '';
DECLARE @PK_COLUMNS NVARCHAR(MAX) = '';
DECLARE @PK_CLUSTERED BIT = 1;
DECLARE @PK_FILEGROUP SYSNAME;
DECLARE @PKPartitionScheme SYSNAME;
DECLARE @PKPartitionSchemeCol SYSNAME;
DECLARE @ALL_COLUMNS NVARCHAR(MAX) = '';
DECLARE @CLIDX_NAME SYSNAME = '';
DECLARE @CLIDX_COLUMNS NVARCHAR(MAX) = '';
DECLARE @CLIDX_UNIQUE BIT;
DECLARE @CLIDX_FILEGROUP SYSNAME;
DECLARE @CLIDXPartitionScheme SYSNAME;
DECLARE @CLIDXPartitionSchemeCol SYSNAME;
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = '';
DECLARE @MaintenanceMode VARCHAR(5) = 'False';
DECLARE @ShadowFilegroupPlacement SYSNAME;
DECLARE @MirrorPartitionScheme VARCHAR(5) = 'False';
DECLARE @TableColumnsDeleted [todoc].[TableColumns];

BEGIN TRY 

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@MaintenanceMode = MAX((CASE WHEN ep.[name] = N'Todoc: Maintenance mode' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ShadowFilegroupPlacement = MAX((CASE WHEN ep.[name] = N'Todoc: Shadow Filegroup Placement' THEN CONVERT(SYSNAME, ep.[value]) ELSE '' END)),
			@MirrorPartitionScheme = MAX((CASE WHEN ep.[name] = N'Todoc: Mirror Partition Scheme' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
		AND ep.[name] IN(N'Todoc: Table name prefix', N'Todoc: Table name postfix', N'Todoc: Maintenance mode',
			N'Todoc: Shadow Filegroup Placement', N'Todoc: Mirror Partition Scheme');

	IF (@MaintenanceMode = 'False')
	BEGIN

		-- build the new PK definition
		SELECT	TOP 1
				@PK_NAME = ISNULL(PKName, ''),
				@PK_CLUSTERED = ISNULL(PKClustered, 0),
				@PK_FILEGROUP = ISNULL(PKFilegroup, ''),
				@PKPartitionScheme = ISNULL(PKPartitionScheme, '')
		FROM @TableColumnsCST
		WHERE PKColumn = 1;

		SELECT @PK_COLUMNS = QUOTENAME(ColumnName) 
			+ (CASE WHEN ISNULL(PKDescOrder, 0) = 1 THEN ' DESC' ELSE 'ASC' END) + ','
		FROM @TableColumnsCST
		WHERE PKColumn = 1;

		SET @PK_COLUMNS = @PK_COLUMNS + '[' + RIGHT(@ShadowTableNamePostfix, LEN(@ShadowTableNamePostfix) - 1) + 'ID] ASC'

		SET @PK_SQL = 'CONSTRAINT [' + @PK_NAME + @ShadowTableNamePostfix + '] 
			PRIMARY KEY ' + (CASE WHEN @PK_CLUSTERED = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END)
			 + ' (' + @PK_COLUMNS + ')' + CHAR(13) + CHAR(13);

		-- now build the deletion type column constraint
		SET @TYPE_DEFAULT_SQL += 'CONSTRAINT [DF_' + @ShadowTableNamePrefix + @TableName 
			+ @ShadowTableNamePostfix + '_DeletionType] DEFAULT (''D'')';

		-- create the table
		SET @SQL = 'CREATE TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix) + '(
			[' + RIGHT(@ShadowTableNamePostfix, LEN(@ShadowTableNamePostfix) - 1) + 'ID] BIGINT IDENTITY(1,1) NOT NULL, [' 
			+ RIGHT(@ShadowTableNamePostfix, LEN(@ShadowTableNamePostfix) - 1) + 'Type] CHAR(1) ' 
			+ @TYPE_DEFAULT_SQL + ' NOT NULL,'
		SELECT @SQL += + QUOTENAME(ColumnName) + ' ' + ShadowTableColumnType + ','
		FROM @TableColumnsCST
		ORDER BY [ColPosition];
		SET @SQL += '[EditingSQLUser] SYSNAME CONSTRAINT [DF_' + @ShadowTableNamePrefix
			+ @TableName + @ShadowTableNamePostfix + '_EditingSQLUser] DEFAULT(SUSER_SNAME()),'
		SET @SQL += @PK_SQL + ')' 

		-- if following partition scheme then follow
		IF(@MirrorPartitionScheme = 'True' AND @PKPartitionScheme <> '')
		BEGIN
			SET @PKPartitionSchemeCol = 
				ISNULL((
				SELECT [ColumnName]
				FROM @TableColumnsCST
				WHERE PKPartitionScheme = @PKPartitionScheme
					AND PKPartitionOrdinal = 1
				), '');
				IF CHARINDEX(@PKPartitionSchemeCol, @PK_COLUMNS) > 0
					SET @SQL += ' ON ' + @PKPartitionScheme + '(' + QUOTENAME(@PKPartitionSchemeCol) + ');';
		END
		ELSE
		BEGIN
			-- table not partitioned, so if mirroring filegroup include an on clause
			-- if shadow filegroup placement set to MIRROR then same as the source table else the given filegroup
			SET @SQL += ' ON ' + QUOTENAME(CASE WHEN @ShadowFilegroupPlacement = 'MIRROR' THEN @PK_FILEGROUP ELSE @ShadowFilegroupPlacement END) + ';';
		END

		-- if there is a clustered index that isn't the pk then put that on as well
		-- but only if it's partitioned
		SELECT	TOP 1
				@CLIDX_NAME = ISNULL(CLIDXName, ''),
				@CLIDX_UNIQUE = ISNULL(CLIDXUnique, 0),
				@CLIDX_FILEGROUP = ISNULL(CLIDXFilegroup, ''),
				@CLIDXPartitionScheme = ISNULL(CLIDXPartitionScheme, '')
		FROM @TableColumnsCST
		WHERE '' <> ISNULL(CLIDXName, '');

		IF (ISNULL(@CLIDX_NAME, '') <> '')
		BEGIN
			SELECT @CLIDX_COLUMNS += QUOTENAME([ColumnName]) 
				+ (CASE WHEN ISNULL([CLIDXDescOrder], 0) = 1 THEN ' DESC' ELSE 'ASC' END) + ','
			FROM @TableColumnsCST
			WHERE [CLIDXName] = @CLIDX_NAME
			ORDER BY [CLIDXColOrdinal];
			SET @CLIDX_COLUMNS += '[' + RIGHT(@ShadowTableNamePostfix, LEN(@ShadowTableNamePostfix) - 1) + 'ID] ASC';

			SET @CLIDX_SQL = 'CREATE ' + (CASE WHEN @CLIDX_UNIQUE = 1 THEN 'UNIQUE' ELSE '' END) 
			+ ' CLUSTERED INDEX [' + @CLIDX_NAME + @ShadowTableNamePostfix + '] 
			ON [' + @TableSchema + '].[' + @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix + ']'
			+ ' (' + @CLIDX_COLUMNS + ')' + CHAR(13) + CHAR(13);
			SET @SQL += @CLIDX_SQL;
			
			-- if following partition scheme then follow
			IF(@MirrorPartitionScheme = 'True')
			BEGIN
				SET @CLIDXPartitionSchemeCol = 
					ISNULL((
					SELECT [ColumnName]
					FROM @TableColumnsCST
					WHERE CLIDXPartitionScheme = @CLIDXPartitionScheme
						AND CLIDXPartitionOrdinal = 1
					), '');
					IF CHARINDEX(@CLIDXPartitionSchemeCol, @CLIDX_COLUMNS) > 0
						SET @SQL += ' ON ' + @CLIDXPartitionScheme + '(' + QUOTENAME(@CLIDXPartitionSchemeCol) + ');';
			END
			ELSE
			BEGIN
				-- table not partitioned, so if mirroring filegroup include an on clause
				-- if shadow filegroup placement set to MIRROR then same as the source table else the given filegroup
				SET @SQL += ' ON ' + QUOTENAME(CASE WHEN @ShadowFilegroupPlacement = 'MIRROR' THEN @CLIDX_FILEGROUP ELSE @ShadowFilegroupPlacement END) + ';';
			END

		END
	END
	ELSE
	BEGIN

		-- in maintenance mode, so do not drop any columns dropped from the table, just make
		-- them nullable and add any new columns

		INSERT INTO @TableColumnsDeleted
		SELECT *
		FROM [todoc].[svTableColumns]
		WHERE TableSchema = @TableSchema
			AND TableName = @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix;

		-- null any dropped columns
		SELECT @SQL +='ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix) 
			+ ' ALTER COLUMN ' + QUOTENAME(ColumnName) + ' ' + REPLACE(TableColumnType, 'NOT NULL', 'NULL')
			+ ';' + CHAR(13)
		FROM @TableColumnsDeleted sht
		WHERE sht.ColumnName NOT IN
				(
				SELECT ColumnName
				FROM @TableColumnsCST
				)
			AND sht.IsNullable = 0
			AND sht.ColPosition > 2
			AND sht.ColumnName NOT IN('EditingSQLUser');

		-- add any new columns
		SELECT @SQL +='ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix) 
			+ ' ADD ' + QUOTENAME(ColumnName) + ' ' + TableColumnType
			+ ';' + CHAR(13)
		FROM @TableColumnsCST mt
		WHERE mt.ColumnName NOT IN
				(
				SELECT ColumnName
				FROM @TableColumnsDeleted
				)
		ORDER BY mt.ColPosition;

		-- alter any columns where the data type has changed
		SELECT @SQL +='ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix) 
			+ ' ALTER COLUMN ' + QUOTENAME(dt.ColumnName) + ' ' + mt.ShadowTableColumnType
			+ ';' + CHAR(13)
		FROM @TableColumnsCST mt
		INNER JOIN @TableColumnsDeleted dt
			ON mt.ColumnName = dt.ColumnName
		WHERE mt.ShadowTableColumnType <> dt.ShadowTableColumnType
		ORDER BY mt.ColPosition;

	END

	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: Table name prefix', @value = N'', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateShadowTable';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: Table name postfix', @value = N'_Shadow', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateShadowTable';
GO


EXECUTE sp_addextendedproperty @name = N'Todoc: Shadow Filegroup Placement', @value = N'MIRROR', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateShadowTable';


GO
EXECUTE sp_addextendedproperty @name = N'Todoc: Mirror Partition Scheme', @value = N'False', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateShadowTable';


GO
EXECUTE sp_addextendedproperty @name = N'Todoc: Maintenance mode', @value = N'False', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateShadowTable';
GO

-- CREATE UPDATE
CREATE PROCEDURE [todoc].[sspCreateUpdateSP]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCUSP [todoc].[TableColumns] READONLY,
	@UsingShadow BIT,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE CREATION OF AN UPDATE STORED PROCEDURE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL_DROP NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @PARAMETER_COLUMNS NVARCHAR(MAX) = '';
DECLARE @COLUMN_LIST_NO_DATA_TYPE NVARCHAR(MAX) = '';
DECLARE @COLUMN_LIST_NO_DATA_TYPE_UPDATE NVARCHAR(MAX) = '';
DECLARE @COLUMNS_NO_DATA_TYPE_QUOTED NVARCHAR(MAX) = '';
DECLARE @PK_COLUMN NVARCHAR(2000) = '';
DECLARE @IDENTITY_COL NVARCHAR(200);
DECLARE @UpdateProcNamePrefix NVARCHAR(20) = '';
DECLARE @UpdateProcNamePostfix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = ''; 

BEGIN TRY 

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@UpdateProcNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@UpdateProcNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateUpdateSP')
		AND ep.[name] IN('Todoc: Procedure name prefix', 'Todoc: Procedure name postfix');

	IF (@UsingShadow = 1)
	BEGIN
		SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
				@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
		FROM sys.extended_properties ep
		WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
			AND ep.[name] IN('Todoc: Table name prefix', 'Todoc: Table name postfix');
	END

	-- string together the pk column(s) with no data type
	SELECT @PK_COLUMN += QUOTENAME(ColumnName) + ' = @' + ColumnName  + ' AND '
	FROM @TableColumnsCUSP
	WHERE PKColumn = 1
	ORDER BY ColPosition;
	SET @PK_COLUMN = SUBSTRING(@PK_COLUMN, 1, LEN(@PK_COLUMN) - 4);

	-- get the identity column if there is one
	SELECT @IDENTITY_COL = '@' + ColumnName
	FROM @TableColumnsCUSP
	WHERE IdentityColumn = 1
	
	-- build the parameter list (all columns and datatypes with defaults)
	SELECT @PARAMETER_COLUMNS += '@' + ColumnName + ' as ' + ParameterType + ',' + CHAR(13) 
	FROM @TableColumnsCUSP
	WHERE IsComputedCol = 0
	ORDER BY ColPosition;
	SET @PARAMETER_COLUMNS = SUBSTRING(@PARAMETER_COLUMNS, 1, LEN(@PARAMETER_COLUMNS) - 2);

	-- get a list of all columns with no datatype (quoted)
	SELECT @COLUMNS_NO_DATA_TYPE_QUOTED += QUOTENAME(ColumnName) + ','
	FROM @TableColumnsCUSP
	ORDER BY ColPosition;
	SET @COLUMNS_NO_DATA_TYPE_QUOTED = SUBSTRING(@COLUMNS_NO_DATA_TYPE_QUOTED, 1, LEN(@COLUMNS_NO_DATA_TYPE_QUOTED) - 1);

	-- get a list of all columns with no datatype minus the pk identity column(s) for update statement
	SELECT @COLUMN_LIST_NO_DATA_TYPE_UPDATE += QUOTENAME(ColumnName) + ' = @' + ColumnName + ','
	FROM @TableColumnsCUSP
	WHERE IsComputedCol = 0
		AND (PKColumn = 0 OR (PKColumn = 1 AND IdentityColumn = 0))
	ORDER BY ColPosition;
	SET @COLUMN_LIST_NO_DATA_TYPE_UPDATE = SUBSTRING(@COLUMN_LIST_NO_DATA_TYPE_UPDATE, 1, LEN(@COLUMN_LIST_NO_DATA_TYPE_UPDATE) - 1);

	-- create and parameters
	SET @SQL += 'CREATE PROC [' + @TableSchema + '].[' + @UpdateProcNamePrefix + @TableName + @UpdateProcNamePostfix + ']' + CHAR(13)
	SET @SQL += '(' + CHAR(13)
	SET @SQL += @PARAMETER_COLUMNS + CHAR(13)
	SET @SQL += ')' + CHAR(13)
	SET @SQL += 'AS' + CHAR(13)
	SET @SQL += '' + CHAR(13)
	
	-- YAML metadata here
	SET @SQL += '/**' + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'summary:	>' + CHAR(13);
	SET @SQL += '				Updates an ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table record' + CHAR(13);
	SET @SQL += ' - code:	EXEC [' + @TableSchema + '].[' + @UpdateProcNamePrefix + @TableName + @UpdateProcNamePostfix + ']' + CHAR(13);
	SET @SQL += '	parameters: ' + REPLACE(REPLACE(@PARAMETER_COLUMNS, CHAR(13), ''), ' as ', ' : ') + CHAR(13);
	IF ISNULL(@IDENTITY_COL, '') = ''
	BEGIN
		SET @SQL += 'returns: 0 if success, otherwise -1' + CHAR(13);
	END
	ELSE
	BEGIN
		SET @SQL += 'returns: passed in ' + REPLACE(@IDENTITY_COL, '@', '') + ' value if success, otherwise -1' + CHAR(13);
	END
	SET @SQL += '**/' + CHAR(13);
	
	-- body
	SET @SQL += 'BEGIN TRANSACTION;' + CHAR(13) + CHAR(13)
	SET @SQL += 'BEGIN TRY' + char(13) + CHAR(13) 
	SET @SQL += '	-- UPDATE' + CHAR(13)
	SET @SQL += '	UPDATE [' + @TableSchema + '].[' + @TableName + '] SET ' + CHAR(13)
	SET @SQL += '	' + @COLUMN_LIST_NO_DATA_TYPE_UPDATE + CHAR(13)
	IF (@UsingShadow = 1)
	BEGIN
		SET @SQL += '	OUTPUT ''U'',' + REPLACE(REPLACE(@COLUMNS_NO_DATA_TYPE_QUOTED, '[', '[DELETED].['), 'DELETED.[ModifiedDate]', 'GETDATE()') + CHAR(13);
		SET @SQL += '	INTO [' + @TableSchema + '].[' + @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix + ']';
		SET @SQL += '([' + REPLACE(@ShadowTableNamePostfix, '_', '') + 'Type], ' + @COLUMNS_NO_DATA_TYPE_QUOTED + ')' + CHAR(13);
	END
	SET @SQL += '	WHERE ' + @PK_COLUMN + ';' + CHAR(13) + CHAR(13);
	SET @SQL += '	COMMIT TRANSACTION;' + CHAR(13) + CHAR(13)
	SET @SQL += '	RETURN ' + ISNULL(@IDENTITY_COL, 0) + ';' + CHAR(13) + CHAR(13)
	SET @SQL += 'END TRY' + CHAR(13) + CHAR(13)
	
	-- catch block
	SET @SQL += 'BEGIN CATCH' + CHAR(13) + CHAR(13)  
	SET @SQL += '	DECLARE @ErrorMessage NVARCHAR(4000);' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorSeverity INT;' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorState INT;' + CHAR(13) + CHAR(13)
	SET @SQL += '	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + CHAR(13) + CHAR(13)
	SET @SQL += '	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);' + CHAR(13) + CHAR(13)
	SET @SQL += '	ROLLBACK TRANSACTION;' + CHAR(13) + CHAR(13) 
	SET @SQL += '	RETURN -1;' + CHAR(13) + CHAR(13) 
	SET @SQL += 'END CATCH;' + CHAR(13) + CHAR(13)
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO
EXECUTE sp_addextendedproperty @name = N'Todoc: Procedure name prefix', @value = N'asp', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateUpdateSP';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: Procedure name postfix', @value = N'_Update', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateUpdateSP';
GO

-- CREATE UPDATE TRIGGER
CREATE PROCEDURE [todoc].[sspCreateUpdateTrigger]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCUT [todoc].[TableColumns] READONLY,
	@UsingShadow BIT,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE CREATION OF AN UPDATE TRIGGER

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @COLUMNS_NO_DATA_TYPE_QUOTED NVARCHAR(MAX) = '';
DECLARE @TriggerNamePrefix NVARCHAR(20) = '';
DECLARE @TriggerNamePostfix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = ''; 

BEGIN TRY

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@TriggerNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@TriggerNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateUpdateTrigger')
		AND ep.[name] IN('Todoc: Trigger name prefix', 'Todoc: Trigger name postfix');

	SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
		AND ep.[name] IN('Todoc: Table name prefix', 'Todoc: Table name postfix');

	-- get a list of all columns with no datatype (quoted)
	SELECT @COLUMNS_NO_DATA_TYPE_QUOTED += QUOTENAME(ColumnName) + ','
	FROM @TableColumnsCUT
	ORDER BY ColPosition;
	SET @COLUMNS_NO_DATA_TYPE_QUOTED = SUBSTRING(@COLUMNS_NO_DATA_TYPE_QUOTED, 1, LEN(@COLUMNS_NO_DATA_TYPE_QUOTED) - 1);

	-- create and parameters
	SET @SQL += 'CREATE TRIGGER [' + @TableSchema + '].[' + @TriggerNamePrefix + @TableName + @TriggerNamePostfix + ']' + CHAR(13)
	SET @SQL += 'ON [' + @TableSchema + '].[' + @TableName + ']' + CHAR(13)
	SET @SQL += 'FOR UPDATE' + CHAR(13)
	SET @SQL += 'AS' + CHAR(13)
	SET @SQL += '' + CHAR(13)
	
	-- YAML metadata here
	SET @SQL += '/**' + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'summary:	>' + CHAR(13);
	SET @SQL += '				Records the update of an ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table record' + CHAR(13);
	SET @SQL += ' - code:	Cannot be called from client code' + CHAR(13);
	SET @SQL += '	parameters: n/a' + CHAR(13);
	SET @SQL += 'returns: on success nothing, otherwise throws an error' + CHAR(13);
	SET @SQL += '**/' + CHAR(13);
	
	-- body
	SET @SQL += 'BEGIN TRY' +  + CHAR(13) +  + CHAR(13)
	SET @SQL += '	INSERT INTO [' + @TableSchema + '].[' + @ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix + ']' + CHAR(13);
	SET @SQL += '	([' + RIGHT(@ShadowTableNamePostfix, LEN(@ShadowTableNamePostfix) - 1) + 'Type],' + @COLUMNS_NO_DATA_TYPE_QUOTED + ')' + CHAR(13);
	SET @SQL += '	SELECT ''U'',' + REPLACE(REPLACE(@COLUMNS_NO_DATA_TYPE_QUOTED, '[', '[DELETED].['), 'DELETED.[ModifiedDate]', 'GETDATE()') + CHAR(13);
	SET @SQL += '	FROM DELETED;' + CHAR(13) + CHAR(13);
	SET @SQL += 'END TRY' + CHAR(13) + CHAR(13)
	
	-- catch block
	SET @SQL += 'BEGIN CATCH' + CHAR(13) + CHAR(13)  
	SET @SQL += '	DECLARE @ErrorMessage NVARCHAR(4000);' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorSeverity INT;' + CHAR(13)
	SET @SQL += '	DECLARE @ErrorState INT;' + CHAR(13) + CHAR(13)
	SET @SQL += '	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + CHAR(13) + CHAR(13)
	SET @SQL += '	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);' + CHAR(13) + CHAR(13) 
	SET @SQL += 'END CATCH;' + CHAR(13) + CHAR(13)
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO
EXECUTE sp_addextendedproperty @name = N'Todoc: Trigger name prefix', @value = N'atr', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateUpdateTrigger';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: Trigger name postfix', @value = N'_Update', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateUpdateTrigger';
GO

-- CREATE VIEW
CREATE PROCEDURE [todoc].[sspCreateView]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCV [todoc].[TableColumns] READONLY,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE CREATION OF A VIEW

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @COLUMNS_NO_DATA_TYPE NVARCHAR(MAX) = '';
DECLARE @ViewNamePrefix NVARCHAR(20) = '';
DECLARE @ViewNamePostfix NVARCHAR(20) = '';

BEGIN TRY 
	
	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@ViewNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: View name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ViewNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: View name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateView')
		AND ep.[name] IN('Todoc: View name prefix', 'Todoc: View name postfix');

	SET @SQL += 'CREATE VIEW [' + @TableSchema + '].[' + @ViewNamePrefix + @TableName + ']' + CHAR(13)

	SELECT @COLUMNS_NO_DATA_TYPE += QUOTENAME(ColumnName) + ',' + CHAR(13)
	FROM @TableColumnsCV
	ORDER BY ColPosition;
	SET @COLUMNS_NO_DATA_TYPE = left(@COLUMNS_NO_DATA_TYPE, len(@COLUMNS_NO_DATA_TYPE) - 2);

	SET @SQL += 'AS' + CHAR(13)
	
	-- YAML metadata here
	SET @SQL += '/**' + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'summary:	>' + CHAR(13);
	SET @SQL += '				Retrieves ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table records' + CHAR(13);
	SET @SQL += ' - code:	SELECT * FROM [' + @TableSchema + '].[' + @ViewNamePrefix + @TableName + ']' + CHAR(13);
	SET @SQL += '	parameters: n/a' + CHAR(13);
	SET @SQL += 'returns: ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table records' + CHAR(13);
	SET @SQL += '**/' + CHAR(13);
	
	-- body here
	SET @SQL += 'SELECT' + CHAR(13)
	SET @SQL += @COLUMNS_NO_DATA_TYPE + CHAR(13)
	SET @SQL += 'FROM [' + @TableSchema + '].[' + @TableName + ']' + CHAR(13) + CHAR(13)

	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO
EXECUTE sp_addextendedproperty @name = N'Todoc: View name prefix', @value = N'av', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateView';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: View name postfix', @value = N'', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateView';
GO

-- DELETE ALL OBJECTS
CREATE PROCEDURE [todoc].[sspDeleteAllObjectsOfType]
(
@ObjectType NVARCHAR(20)
)
--WITH EXECUTE AS 'todocuser'
AS
/*
DELETES ALL TODOC OBJECTS OF A GIVEN TYPE FROM THE DATABASE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SET NOCOUNT ON;

-- reject calls to delete anything we don't know about
IF (@ObjectType NOT IN('DeleteSP', 'InsertSP', 'SaveSP', 'Shadow', 
	'UpdateSP', 'View', 'DeleteTrigger', 'UpdateTrigger', 'InsertTrigger'))
BEGIN
	PRINT 'The @ObjectType parameter must be one the following values: DeleteSP, InsertSP, SaveSP, Shadow, UpdateSP, View, DeleteTrigger, UpdateTrigger, or InsertTrigger';
	RETURN;
END

DECLARE @SQL_DROP NVARCHAR(MAX) = '';
DECLARE @TableSchema SYSNAME;
DECLARE @TableName SYSNAME;

BEGIN TRANSACTION;

BEGIN TRY

	DECLARE TodocTables CURSOR
	READ_ONLY FORWARD_ONLY LOCAL STATIC
	FOR
	SELECT	nht.TableSchema, 
			nht.TableName
	FROM [todoc].[svNonHeapTables] nht
	INNER JOIN [todoc].[svActiveTables] at
		ON nht.[schema_id] = at.[schema_id]
		AND nht.[object_id] = at.[object_id]
	ORDER BY nht.TableSchema, nht.TableName;

	OPEN TodocTables

	PRINT 'Deleting todoc ' + @ObjectType + ' for:'

	FETCH NEXT FROM TodocTables INTO @TableSchema, @TableName
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN

			PRINT @TableSchema + '.' + @TableName;

			-- call the relevant delete function based on @ObjectType and execute
			IF (@ObjectType = 'DeleteSP')
			BEGIN
				EXEC [todoc].[sspDropDeleteSP] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'InsertSP')
			BEGIN
				EXEC [todoc].[sspDropInsertSP] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'SaveSP')
			BEGIN
				EXEC [todoc].[sspDropSaveSP] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'Shadow')
			BEGIN
				EXEC [todoc].[sspDropShadowTable] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'UpdateSP')
			BEGIN
				EXEC [todoc].[sspDropUpdateSP] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'View')
			BEGIN
				EXEC [todoc].[sspDropView] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'DeleteTrigger')
			BEGIN
				EXEC [todoc].[sspDropDeleteTrigger] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'UpdateTrigger')
			BEGIN
				EXEC [todoc].[sspDropUpdateTrigger] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'InsertTrigger')
			BEGIN
				EXEC [todoc].[sspDropInsertTrigger] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END

		END
		FETCH NEXT FROM TodocTables INTO @TableSchema, @TableName
	END

	CLOSE TodocTables
	DEALLOCATE TodocTables;

	COMMIT TRANSACTION;

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	ROLLBACK TRANSACTION;

	RETURN -1;

END CATCH;
GO

-- DELETE OBJECTS FOR A GIVEN TABLE
CREATE PROCEDURE [todoc].[sspDeleteObjectsForTable]
(
@TableSchema SYSNAME,
@TableName SYSNAME
)
--WITH EXECUTE AS 'todocuser'
AS
/*
DELETES ALL TODOC OBJECTS FOR A GIVEN TABLE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SET NOCOUNT ON;

DECLARE @HasView NVARCHAR(5) = 'False';
DECLARE @HasShadowTable NVARCHAR(5) = 'False';
DECLARE @HasDeleteProc NVARCHAR(5) = 'False';
DECLARE @HasInsertProc NVARCHAR(5) = 'False';
DECLARE @HasUpdateProc NVARCHAR(5) = 'False';
DECLARE @HasSaveProc NVARCHAR(5) = 'False';

BEGIN TRANSACTION;

BEGIN TRY

	-- see what deletes we to do on this table
	SELECT	@HasView = MAX((CASE WHEN ep.[name] = 'Todoc: Has View' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasShadowTable = MAX((CASE WHEN ep.[name] = 'Todoc: Has Shadow Table' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasDeleteProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Delete Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasInsertProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Insert Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasUpdateProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Update Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasSaveProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Save Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID(CONVERT(nvarchar(128), @TableSchema) + '.' + CONVERT(nvarchar(128), @TableName))
		AND ep.[name] LIKE 'Todoc: Has%';

	-- call the relevant delete functions
	IF (@HasView = 'True')
	BEGIN
		EXEC [todoc].[sspDropView] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasShadowTable = 'True')
	BEGIN
		EXEC [todoc].[sspDropShadowTable] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasDeleteProc = 'True')
	BEGIN
		EXEC [todoc].[sspDropDeleteSP] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasInsertProc = 'True')
	BEGIN
		EXEC [todoc].[sspDropInsertSP] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasUpdateProc = 'True')
	BEGIN
		EXEC [todoc].[sspDropUpdateSP] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasSaveProc = 'True')
	BEGIN
		EXEC [todoc].[sspDropSaveSP] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	COMMIT TRANSACTION;

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	ROLLBACK TRANSACTION;

	RETURN -1;

END CATCH;
GO

-- DROP DELETE
CREATE PROCEDURE [todoc].[sspDropDeleteSP]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
DROPS A DELETE STORED PROCEDURE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @DeletedProcNamePrefix NVARCHAR(20) = '';
DECLARE @DeletedProcNamePostfix NVARCHAR(20) = '';

BEGIN TRY

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@DeletedProcNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@DeletedProcNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateDeleteSP')
		AND ep.[name] IN('Todoc: Procedure name prefix', 'Todoc: Procedure name postfix');

	-- generate the drop
	SET @SQL += 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @TableSchema + '].[' + @DeletedProcNamePrefix + @TableName + @DeletedProcNamePostfix + ']'') AND type in (N''P'', N''PC''))' + CHAR(13);
	SET @SQL += 'DROP PROCEDURE [' + @TableSchema + '].[' + @DeletedProcNamePrefix + @TableName + @DeletedProcNamePostfix + '];' + CHAR(13)+ CHAR(13);
  
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

-- DROP DELETE TRIGGER
CREATE PROCEDURE [todoc].[sspDropDeleteTrigger]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF A DELETE TRIGGER

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @TriggerNamePrefix NVARCHAR(20) = '';
DECLARE @TriggerNamePostfix NVARCHAR(20) = '';

BEGIN TRY

	-- get the postfix we're using
	SELECT	@TriggerNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@TriggerNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateDeleteTrigger')
		AND ep.[name] IN('Todoc: Trigger name prefix', 'Todoc: Trigger name postfix');

	-- generate the drop
	SET @SQL += 'IF  EXISTS (SELECT * FROM sys.Triggers WHERE object_id = OBJECT_ID(N''[' + @TableSchema + '].[' + @TriggerNamePrefix + @TableName + @TriggerNamePostfix + ']'') AND type = N''TR'')' + CHAR(13);
	SET @SQL += 'DROP TRIGGER [' + @TableSchema + '].[' + @TriggerNamePrefix + @TableName + @TriggerNamePostfix + '];' + CHAR(13)+ CHAR(13);
  
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

-- DROP INSERT
CREATE PROCEDURE [todoc].[sspDropInsertSP]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF AN INSERT STORED PROCEDURE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @InsertProcNamePrefix NVARCHAR(20) = '';
DECLARE @InsertProcNamePostfix NVARCHAR(20) = '';

BEGIN TRY

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@InsertProcNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@InsertProcNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateInsertSP')
		AND ep.[name] IN('Todoc: Procedure name prefix', 'Todoc: Procedure name postfix');

	-- generate the drop
	SET @SQL += 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @TableSchema + '].[' + @InsertProcNamePrefix + @TableName + @InsertProcNamePostfix + ']'') AND type in (N''P'', N''PC''))' + char(13)
	SET @SQL += 'DROP PROCEDURE [' + @TableSchema + '].[' + @InsertProcNamePrefix + @TableName + @InsertProcNamePostfix + '];' + CHAR(13) + CHAR(13);
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

-- DROP INSERT TRIGGER
CREATE PROCEDURE [todoc].[sspDropInsertTrigger]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF A INSERT TRIGGER

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @TriggerNamePrefix NVARCHAR(20) = '';
DECLARE @TriggerNamePostfix NVARCHAR(20) = '';

BEGIN TRY

	-- get the postfix we're using
	SELECT	@TriggerNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@TriggerNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateInsertTrigger')
		AND ep.[name] IN('Todoc: Trigger name prefix', 'Todoc: Trigger name postfix');

	-- generate the drop
	SET @SQL += 'IF  EXISTS (SELECT * FROM sys.Triggers WHERE object_id = OBJECT_ID(N''[' + @TableSchema + '].[' + @TriggerNamePrefix + @TableName + @TriggerNamePostfix + ']'') AND type = N''TR'')' + CHAR(13);
	SET @SQL += 'DROP TRIGGER [' + @TableSchema + '].[' + @TriggerNamePrefix + @TableName + @TriggerNamePostfix + '];' + CHAR(13)+ CHAR(13);
  
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

-- DROP SAVE
CREATE PROCEDURE [todoc].[sspDropSaveSP]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF A SAVE (COMBINED INSERT/UPDATE) STORED 
PROCEDURE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @SaveProcNamePrefix NVARCHAR(20) = '';
DECLARE @SaveProcNamePostfix NVARCHAR(20) = '';

BEGIN TRY

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@SaveProcNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@SaveProcNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateSaveSP')
		AND ep.[name] IN('Todoc: Procedure name prefix', 'Todoc: Procedure name postfix');

	-- generate the drop
	SET @SQL += 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @TableSchema + '].[' + @SaveProcNamePrefix + @TableName + @SaveProcNamePostfix + ']'') AND type in (N''P'', N''PC''))' + char(13)
	SET @SQL += 'DROP PROCEDURE [' + @TableSchema + '].[' + @SaveProcNamePrefix + @TableName + @SaveProcNamePostfix + '];' + CHAR(13) + CHAR(13);
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

-- DROP SHADOW/AUDIT
CREATE PROCEDURE [todoc].[sspDropShadowTable]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF A SHADOW/AUDIT TABLE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = '';
DECLARE @MaintenanceMode NVARCHAR(5) = 'False';

BEGIN TRY

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = 'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = 'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@MaintenanceMode = MAX((CASE WHEN ep.[name] = 'Todoc: Maintenance mode' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
		AND ep.[name] IN('Todoc: Table name postfix', 'Todoc: Maintenance mode');

	IF (@MaintenanceMode = 'True')
		RETURN 0;

	-- create the drop
	SET @SQL = 
	'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix) + ''') 
	AND TYPE IN (N''U'')) DROP TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix) + ';' + CHAR(13) + CHAR(13);

	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

-- DROP UPDATE
CREATE PROCEDURE [todoc].[sspDropUpdateSP]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF AN UPDATE STORED PROCEDURE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @UpdateProcNamePrefix NVARCHAR(20) = '';
DECLARE @UpdateProcNamePostfix NVARCHAR(20) = '';

BEGIN TRY

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@UpdateProcNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@UpdateProcNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateUpdateSP')
		AND ep.[name] IN('Todoc: Procedure name prefix', 'Todoc: Procedure name postfix');

	-- generate the drop
	SET @SQL += 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @TableSchema + '].[' + @UpdateProcNamePrefix + @TableName + @UpdateProcNamePostfix + ']'') AND type in (N''P'', N''PC''))' + char(13)
	SET @SQL += 'DROP PROCEDURE [' + @TableSchema + '].[' + @UpdateProcNamePrefix + @TableName + @UpdateProcNamePostfix + '];' + CHAR(13) + CHAR(13)
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

-- DROP UPDATE TRIGGER
CREATE PROCEDURE [todoc].[sspDropUpdateTrigger]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF AN UPDATE TRIGGER

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @TriggerNamePrefix NVARCHAR(20) = '';
DECLARE @TriggerNamePostfix NVARCHAR(20) = '';

BEGIN TRY

	-- get the postfix we're using
	SELECT	@TriggerNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@TriggerNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Trigger name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateUpdateTrigger')
		AND ep.[name] IN('Todoc: Trigger name prefix', 'Todoc: Trigger name postfix');

	-- generate the drop
	SET @SQL += 'IF  EXISTS (SELECT * FROM sys.Triggers WHERE object_id = OBJECT_ID(N''[' + @TableSchema + '].[' + @TriggerNamePrefix + @TableName + @TriggerNamePostfix + ']'') AND type = N''TR'')' + CHAR(13);
	SET @SQL += 'DROP TRIGGER [' + @TableSchema + '].[' + @TriggerNamePrefix + @TableName + @TriggerNamePostfix + '];' + CHAR(13)+ CHAR(13);
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

-- DROP VIEW
CREATE PROCEDURE [todoc].[sspDropView]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF A VIEW

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @ViewNamePrefix NVARCHAR(20) = '';
DECLARE @ViewNamePostfix NVARCHAR(20) = '';

BEGIN TRY
	
	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@ViewNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: View name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ViewNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: View name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateView')
		AND ep.[name] IN('Todoc: View name prefix', 'Todoc: View name postfix');
	
	-- generate the drop
	SET @SQL += 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @TableSchema + '].[' + @ViewNamePrefix + @TableName + @ViewNamePostfix + ']'') AND type = N''V'')' + CHAR(13)
	SET @SQL += 'DROP VIEW [' + @TableSchema + '].[' + @ViewNamePrefix + @TableName + @ViewNamePostfix + '];' + CHAR(13) + CHAR(13);

	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO

CREATE TRIGGER [dbtTodocAdministerObjects]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
/*
CAPTURES CHANGES TO TODOC ENABLED CHANGES AND AUTO-ADMINISTRATION OF THE 
ASSOCIATED TODOC OBJECTS

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
BEGIN
	SET NOCOUNT ON;

	DECLARE @EventType NVARCHAR(128);
	DECLARE @TableSchema SYSNAME;
	DECLARE @TableName SYSNAME;

	--IF (CHARINDEX('YOURPRODUCTIONSERVER', @@SERVERNAME)) > 0
	--BEGIN

		SELECT	@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME'),
				@TableSchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
				@TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

		IF NOT EXISTS (SELECT * FROM sys.extended_properties ep
			WHERE ep.major_id = OBJECT_ID(@TableSchema + '.' + @TableName)
			AND ep.class_desc = 'OBJECT_OR_COLUMN'
			AND ep.name = 'Todoc: Enabled'
			AND ep.value = 'True')
			RETURN;

		-- if the table has been altered then run it through todoc
		IF (@EventType = 'ALTER_TABLE')
		BEGIN

			EXEC [todoc].[sspAdministerObjects] 
				@TableSchema = @TableSchema,
				@TableName = @TableName;
		END

		-- if the table has been droped then drop all todoc objects
		IF (@EventType = 'DROP_TABLE')
		BEGIN
			EXEC [todoc].[sspDeleteObjectsForTable] 
				@TableSchema = @TableSchema,
				@TableName = @TableName;
		END

	--END
	--ELSE
	--BEGIN
	--	RAISERROR(N'No direct changes allowed to objects in this database.', 10, 1); 
	--	ROLLBACK;
	--END

END;
GO







